package ortus.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import org.h2.tools.Server;

/**
 *
 * @author jphipps
 */
public class h2engine extends ortus.ortusvars implements IEngine {
    private DBAccess dba = new DBAccess();
    private String h2driver = "org.h2.Driver";
    private String h2protocol = ortus.api.GetProperty("h2protocol", "jdbc:h2:ortusDB/ortusDB");
//    private String h2protocol = "jdbc:h2:ortusDB" + java.io.File.separator;
//    private String h2netprotocol = "jdbc:h2:tcp://192.168.0.15/Program Files/SageTV/SageTV/ortusDB" + java.io.File.separator;
//    private String h2netprotocol = "jdbc:h2:tcp://192.168.0.25/h2/ortusDB" + java.io.File.separator;
//    private JdbcConnectionPool cp = null;
    private Connection gconn = null;
    public boolean indexMediaRunning = false;
    private boolean tcpserver_running = false;
    private Server tcpserver;
    private boolean webserver_running = false;
    private Server webserver;
    private boolean pgserver_running = false;
    private Server pgserver;

    public h2engine () {

	ortus.api.DebugLog(INFO, "OrtusDB: Connecting to: " + h2protocol);
        if ( ! connectDB(h2protocol+";CACHE_SIZE=262144","sage","sage") )
            return;

	if ( ortus.api.GetProperty("dbtcpserver_start", "yes").equals("yes")) {
		StartTcpServer();
	}

	if ( ortus.api.GetProperty("dbwebserver_start", "yes").equals("yes")) {
		StartWebServer();
	}

	if ( ortus.api.GetProperty("dbpgserver_start", "no").equals("yes")) {
		StartPgServer();
	}

//        Runtime.getRuntime().addShutdownHook(new Thread() {
//           public void run() {
//               ShutdownDatabase();
//           }
//        });
    }

    public void ShutdownDatabase() {
        ortus.api.DebugLog(INFO, "Shutting down ortusDB connection");
        try {
             tcpserver.stop();
             webserver.stop();
	     gconn.close();
//             cp.dispose();
        } catch (Exception ex ) {}
    }

    private boolean connectDB (String connectinfo, String user, String passwd) {
        ortus.api.DebugLog(INFO,"Loading ortusdb");
        if ( ! loadDriver()) {
            ortus.api.DebugLog(INFO, "h2 jar files are missing");
            return false;
        }

        try {
           Properties props = new Properties(); // connection properties
           props.put("user", user);
           props.put("password", passwd);
           gconn = DriverManager.getConnection(connectinfo, props);
//	   cp = JdbcConnectionPool.create(connectinfo, "sage","sage");
           gconn.setAutoCommit(true);
//	   cp.setMaxConnections(20);

//	   ortus.api.DebugLog(INFO,"JDBCPool: Max Connections: " + cp.getMaxConnections());

        } catch( Exception e ) {
            ortus.api.DebugLog(ERROR, "SQLEexception: " + e);
	    return false;
        }
        return true;
    }
    
    private boolean loadDriver() {
        String driver = null;
        try {
		driver = h2driver;
		Class.forName(driver);
		ortus.api.DebugLog(INFO, "ortusDB: Successfully Loaded Embedded Driver");
        } catch (ClassNotFoundException cnfe) {
            ortus.api.DebugLog( ERROR,"Unable to load the JDBC driver " + driver);
            ortus.api.DebugLog( ERROR,"Please check your CLASSPATH.");
            return false;
        }
        return true;
    }

    public void StartTcpServer() {
        if ( tcpserver_running )
            return;

        try {
        String TcpPort = ortus.api.GetProperty("dbtcpserver","9092");
        ortus.api.DebugLog(INFO, "ortusDB: Starting Tcp Server on port: " + TcpPort);
        tcpserver = Server.createTcpServer(new String[] { "-tcpAllowOthers", "-tcpPort" , TcpPort } ).start();
        tcpserver_running = true;
        } catch (Exception e) {
            ortus.api.DebugLog(ERROR, "ortusDB: Tcp server failed to start");
        }
    }

    public void StopTcpServer() {
        if ( tcpserver_running ) {
            tcpserver.stop();
            tcpserver_running = false;
        }
    }

    public boolean IsTcpServerRunning() {
        return tcpserver_running;
    }

    public void StartWebServer() {
        if ( webserver_running )
            return;

        try {
        String WebPort = ortus.api.GetProperty("dbwebserver","8082");
        ortus.api.DebugLog(INFO, "ortusDB: Starting Web Server on port: " + WebPort);
        webserver = Server.createWebServer(new String[] { "-webAllowOthers" , "-webPort" , WebPort } ).start();
        webserver_running = true;
        } catch (Exception e) {
            ortus.api.DebugLog(ERROR, "ortusDB: Web server failed to start");
        }
    }

    public void StopWebServer() {
        if ( webserver_running ) {
            webserver.stop();
            webserver_running = false;
        }
    }

    public boolean IsWebServerRunning() {
        return webserver_running;
    }

     public void StartPgServer() {
        if ( pgserver_running )
            return;

        try {
        String PgPort = ortus.api.GetProperty("dbpgserver","5435");
        ortus.api.DebugLog(INFO, "ortusDB: Starting Web Server on port: " + PgPort);
        pgserver = Server.createPgServer(new String[] { "-pgPort" , PgPort } ).start();
        pgserver_running = true;
        } catch (Exception e) {
            ortus.api.DebugLog(ERROR, "ortusDB: Pg server failed to start");
        }
    }

    public void StopPgServer() {
        if ( pgserver_running ) {
            pgserver.stop();
            pgserver_running = false;
        }
    }

    public boolean IsPgServerRunning() {
        return pgserver_running;
    }

    public Connection GetConnection() {
		try {		
			if ( gconn == null) {
				ortus.api.DebugLog(ERROR, "ortusDB: Connection is null, re-openning");
				connectDB(h2protocol+";CACHE_SIZE=262144","sage","sage");
			} else if ( gconn.isClosed()) {
				ortus.api.DebugLog(ERROR, "ortusDB: Connection is closed, re-openning");
				connectDB(h2protocol+";CACHE_SIZE=262144","sage","sage");
			}

			return gconn;
		} catch (Exception ex) {
			ortus.api.DebugLog(ERROR,"SQLException: GetConnection: " + ex);
			return null;
		}
    }

 public List<Object> GetStatus() {
	 List<Object> status = new ArrayList<Object>();

	 List<List> result = executeSQLQueryArray("call database()");
	 if ( result.size() > 0)
		 status.add("Database Name: " + result.get(0).get(0));
	 result = executeSQLQueryArray("call database_path()");
	 if ( result.size() > 0)
		 status.add("Database Path: " + result.get(0).get(0));
	 result = executeSQLQueryArray("call memory_used()");
	 if ( result.size() > 0)
		 status.add("Memory Used: " + result.get(0).get(0));
	 result = executeSQLQueryArray("call memory_free()");
	 if ( result.size() > 0)
		 status.add("Memory Free: " + result.get(0).get(0));
	 result = executeSQLQueryArray("select count(*) from INFORMATION_SCHEMA.SESSIONS");
	 if ( result.size() > 0)
		 status.add("Active Sessions: " + result.get(0).get(0));
	 result = executeSQLQueryArray("select count(*) from sage.media");
	 if ( result.size() > 0)
		 status.add("Total Media Objects: " + result.get(0).get(0));
	 status.add("Procotol: " + h2protocol);

	 return status;
 }
    public int executeSQL(String sql) {
	  return dba.executeSQL(GetConnection(), sql);
    }
    public List<Object> executeSQLQuery(String sql) {
	  return dba.executeSQLQuery(GetConnection(), sql);
    }
    public List<Object> executeSQLQueryCache(String sql) {
	  return dba.executeSQLQueryCache(GetConnection(), sql);
    }
    public List<List> executeSQLQueryArray(String sql) {
	  return dba.executeSQLQueryArray(GetConnection(), sql);
    }
    public List<List> executeSQLQueryArrayCache(String sql) {
	  return dba.executeSQLQueryArrayCache(GetConnection(), sql);
    }
    public List<HashMap> executeSQLQueryHash(String sql) {
	  return dba.executeSQLQueryHash(GetConnection(), sql);
    }
    public List<HashMap> executeSQLQueryHashCache(String sql) {
	  return dba.executeSQLQueryHashCache(GetConnection(), sql);
    }

    public List<Object> getMediaFilesSQL(String sql) {
	  return dba.getMediaFilesSQL(GetConnection(), sql);
    }

    public List<Object> getSageMediaFilesSQL(String sql) {
	    return dba.getSageMediaFilesSQL(GetConnection(), sql);
    }
    public boolean createDB() {
	  return dba.createDB(GetConnection());
    }
    
    public boolean backupDB() {

       ortus.api.DebugLog(INFO, "Backup of sageDB successful");
       return true;
    }

    public boolean restoreDB(String backupdir) {

        ortus.api.DebugLog(INFO," ortusDB: Restore starting from backup: " + backupdir);

        ShutdownDatabase();

        if ( ! connectDB(h2protocol+"sageDB;restoreFrom=" + backupdir,"sage","sage") ) {
            ortus.api.DebugLog(ERROR,"ortusDB: Restore failure");
            return false;
        }

        ortus.api.DebugLog(INFO, "ortusDB: Restore successful");

        return true;
    }
}
